﻿Imports System
Imports System.ComponentModel
Imports System.Collections
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data




Public Class CFLNotificacionDB
 Implements IDisposable


 Private _ConexionString As String
 Private _ConexionStringAdmon As String
 Private _ConexionEjecucion As SqlConnection
 Private _ComandoSQL As SqlCommand

 private Const __QUEUE_NOTIFICATION__ AS string = "QFuncionesLiquidez"
 private Const __SERVICE_NOTIFICATION__ AS string = "SFuncionesLiquidez"


Public ReadOnly Property ConexionStringAdmon() As String
    Get
        Return CEncripcion.Decrypt(ConfigurationManager.ConnectionStrings("SqlConexionBroker").ConnectionString, "AJnx8d73") 'Se obtiene la cadena de conexión		
    End Get
End Property

Public ReadOnly Property ConexionString() As String
    Get
        Return CEncripcion.Decrypt(ConfigurationManager.ConnectionStrings("SqlConexion").ConnectionString, "AJnx8d73") 'Se obtiene la cadena de conexión		
    End Get
End Property

Public ReadOnly Property ConexionEjecucion() As SqlConnection
    Get
         If IsNothing(_ConexionEjecucion) Then
             _ConexionEjecucion = New SqlConnection(_ConexionString)
         End If
        Return _ConexionEjecucion
    End Get
End Property

Public Property ComandoSQL() As SqlCommand
    Get
        Return _ComandoSQL
    End Get
    Set(ByVal value As SqlCommand)
        _ComandoSQL = value
    End Set
End Property


Public Sub New()
   'SqlDependency.Start(Me.ConexionString,__QUEUE_NOTIFICATION__)
         Try
            'SqlDependency.Start(Me.ConexionString)
            SqlDependency.Start(Me.ConexionStringAdmon)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

End Sub


Private Event _NuevoMsg As EventHandler(Of SqlNotificationEventArgs)

Public Custom Event NuevoMsg As EventHandler(Of SqlNotificationEventArgs)
    AddHandler(ByVal value As EventHandler(Of SqlNotificationEventArgs))
         AddHandler Me._NuevoMsg, value
    End AddHandler

    RemoveHandler(ByVal value As EventHandler(Of SqlNotificationEventArgs))
        RemoveHandler Me._NuevoMsg, value
    End RemoveHandler

    RaiseEvent(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)
    End RaiseEvent
End Event

Public Function RegistrarDependencia() As DataTable

 Me._ComandoSQL = New sqlcommand("SELECT ID, MENSAJE, FECHORA FROM FL_NOTIFICACION") 
 Me._ComandoSQL .Notification = Nothing

 Dim dependencia as SqlDependency = New SqlDependency(Me._ComandoSQL)
 AddHandler dependencia.OnChange, AddressOf  Dependencia_OnChange



 If Me.ConexionEjecucion.State = ConnectionState.Closed
    Me.ConexionEjecucion.ConnectionString = Me.ConexionString
    Me.ConexionEjecucion.Open()
 End If


 Me._ComandoSQL.Connection = Me.ConexionEjecucion 
 Try

     Dim dt As DataTable = New datatable()
     dt.Load(Me._ComandoSQL.ExecuteReader(CommandBehavior.CloseConnection))
     Return dt

 Catch ex As Exception
     
     Return Nothing 
 End Try

End Function



Public Sub Dependencia_OnChange(sender As Object, e As SqlNotificationEventArgs)    
   RaiseEvent _NuevoMsg(sender,e)
End Sub

 public Shared sub CreatePermission()
       Try
          Dim perm As SqlClientPermission = New SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted)
          perm.Demand()
       Catch ex As Exception
           
       End Try

End Sub




private Sub Dispose() Implements IDisposable.Dispose
  'SqlDependency.Stop(Me.ConexionString)
    SqlDependency.Stop(Me.ConexionStringAdmon)
End Sub


Protected Overrides Sub Finalize()
        'SqlDependency.Stop(Me.ConexionString)
        SqlDependency.Stop(Me.ConexionStringAdmon)
        MyBase.Finalize()
    End Sub
End Class
